﻿using aier.dataaccess.model;
using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{

    public class SchoolDal
    {
        public ILog log { get; set; }
        public DapperHelper dapperHelper { get; set; }

        /// <summary>
        /// 通过视图获取所有学校信息
        /// </summary>
        /// <returns></returns>
        public List<SchoolView> QueryAllSchool()
        {
            List<SchoolView> schools = null;
            SqlBuilder sqlBuilder = new SqlBuilder();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_SCHOOL_INFO ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                schools = a.Query<SchoolView>(sqlTemplate.RawSql).ToList();
            });

            return schools;
        }

        /// <summary>
        /// 根据学校ID查询视图信息
        /// </summary>
        /// <param name="shoolId"></param>
        /// <returns></returns>
        public SchoolView QuerySchoolId(string shoolId)
        {
            SchoolView schoolView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_SCHOOL_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where(" s.new_schoolId=@new_schoolId and s.statecode = 0 ");
                sqlParams.Add("@new_schoolId", shoolId);

                schoolView = a.QueryFirstOrDefault<SchoolView>(sqlTemplate.RawSql, sqlParams);
            });

            return schoolView;
        }

        /// <summary>
        /// 根据学校名称查询学校视图信息
        /// </summary>
        /// <param name="communityId"></param>
        /// <returns></returns>
        public List<SchoolView> QuerySchoolByName(string schoolname)
        {
            List<SchoolView> schoolView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_SCHOOL_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where("  CHARINDEX('@new_name',s.new_name)>0 and s.statecode = 0 ");
                sqlParams.Add("@new_name", schoolname);

                schoolView = a.Query<SchoolView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return schoolView;
        }

        /// <summary>
        /// 根据省市区/县ID查询学校视图信息
        /// </summary>
        /// <param name="communityId"></param>
        /// <returns></returns>
        public List<SchoolView> QuerySchoolByProviceCityCounty(string provice,string city,string county,string hospital)
        {
            List<SchoolView> schoolView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select new_name,new_schoolId,OwnerId  from AIER_SCHOOL_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                sqlBuilder.Where(" s.statecode=0 and  new_hospital=@new_hospital and s.new_provinceName=@new_province");
                sqlParams.Add("@new_province", provice);
                if (!string.IsNullOrWhiteSpace(city))
                {
                    sqlBuilder.Where(" s.new_cityName=@new_city");
                    sqlParams.Add("@new_city", city);
                }

                if (!string.IsNullOrWhiteSpace(county))
                {
                    sqlBuilder.Where(" s.new_areaName=@new_area");
                    sqlParams.Add("@new_area", county);
                }

                sqlParams.Add("@new_hospital", hospital);
                schoolView = a.Query<SchoolView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return schoolView;
        }

        /// <summary>
        /// 根据不同查询条件查询学校对像,
        /// 查询条件为：省ID,市ID,区/县ID,层级,学校ID,学校名称,学段,医院ID
        /// 原程序 querySchool
        /// </summary>
        /// <param name="school">学校对像</param>
        /// <returns></returns>
        public IEnumerable<SchoolView> QuerySchool(SchoolView school)
        {
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<SchoolView>>(a =>
              {
                  string sql = @"select * from AIER_SCHOOL_INFO s /**where**/ ";
                  var sqlTemplate = sqlBuilder.AddTemplate(sql);

                  if (null != school.new_provinceName)
                  {
                      sqlBuilder.Where(" s.new_provinceName=@new_province ");
                      sqlParams.Add("@new_province", school.new_provinceName);
                  }

                  if (!string.IsNullOrWhiteSpace(school.new_cityName))
                  {
                      sqlBuilder.Where(" s.new_cityName=@new_city ");
                      sqlParams.Add("@new_city", school.new_cityName);
                  }

                  if (!string.IsNullOrWhiteSpace(school.new_areaName))
                  {
                      sqlBuilder.Where(" s.new_areaName=@new_area ");
                      sqlParams.Add("@new_area", school.new_areaName);
                  }

                  if (school.new_level != null)
                  {
                      sqlBuilder.Where(" s.new_level=@new_level ");
                      sqlParams.Add("@new_level", school.new_level);
                  }

                  if (Guid.Empty != school.new_schoolId && null != school.new_schoolId)
                  {
                      sqlBuilder.Where(" s.new_schoolId=@new_schoolId ");
                      sqlParams.Add("@new_schoolId", school.new_schoolId);
                  }

                  if (!string.IsNullOrEmpty(school.new_name))
                  {
                      sqlBuilder.Where(" charindex(@new_name,s.new_name)>0 ");
                      sqlParams.Add("@new_name", school.new_name);
                  }

                  if (null != school.new_stage)
                  {
                      sqlBuilder.Where(" s.new_stage=@new_stage ");
                      sqlParams.Add("@new_stage", school.new_stage);
                  }

                  if (Guid.Empty != school.new_hospital&&null!= school.new_hospital)
                  {
                      sqlBuilder.Where(" s.new_hospital=@new_hospital ");
                      sqlParams.Add("@new_hospital", school.new_hospital);
                  }

                  log.Info(string.Format("SchoolDal对像中,QuerySchool ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                  return a.Query<SchoolView>(sqlTemplate.RawSql,sqlParams);
              });
        }

        /// <summary>
        /// 根据不同查询统计学校对像数量,
        /// 查询条件为：省ID,市ID,区/县ID,街道ID,学校ID,学校名称
        /// </summary>
        /// <param name="school">学校对像</param>
        /// <returns></returns>
        public int QuerySchoolCount(SchoolView school)
        {
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction<int>(a =>
            {
                string sql = @"select count(*) from dbo.new_school s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                if (null != school.new_province)
                {
                    sqlBuilder.Where(" s.new_province=@new_province ");
                    sqlParams.Add("@new_province", school.new_province);
                }

                if (null != school.new_city)
                {
                    sqlBuilder.Where(" s.new_city=@new_city ");
                    sqlParams.Add("@new_city", school.new_city);
                }

                if (null != school.new_area)
                {
                    sqlBuilder.Where(" s.new_area=@new_area ");
                    sqlParams.Add("@new_area", school.new_area);
                }

                if (school.new_street != null)
                {
                    sqlBuilder.Where(" s.new_street=@new_street ");
                    sqlParams.Add("@new_street", school.new_street);
                }

                if (null != school.new_schoolId)
                {
                    sqlBuilder.Where(" s.new_schoolId=@new_schoolId ");
                    sqlParams.Add("@new_schoolId", school.new_schoolId);
                }

                if (!string.IsNullOrEmpty(school.new_name))
                {
                    sqlBuilder.Where(" s.new_name = @new_name ");
                    sqlParams.Add("@new_name", school.new_name);
                }
                log.Info(string.Format("SchoolDal对像中,QuerySchoolCount方法 ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                return a.QueryFirst<int>(sqlTemplate.RawSql,sqlParams);
            });
        }
    }

    static class DynamicParametersExtensions
    {
        public static string ParametersToString(this DynamicParameters parameters,string sql)
        {
            foreach (string parameterName in parameters.ParameterNames)
            {
                object parameInfo = parameters.Get<object>(parameterName);
                if(parameInfo!=null) sql = sql.Replace(string.Concat("@", "'", parameterName, "'"), parameInfo.ToString());
            }

            return sql;
        }
    }
}
